package com.bootdo.output.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.bootdo.common.domain.DictDO;
import com.bootdo.common.service.DictService;
import com.bootdo.output.domain.ExcelResult;
import com.bootdo.output.domain.OutStorageDO;
import com.bootdo.output.domain.OutStorageDetailDO;
import com.bootdo.output.service.OutStorageDetailService;
import com.bootdo.output.service.OutStorageService;
import com.bootdo.provider.domain.GoodsInfoDO;
import com.bootdo.provider.service.GoodsInfoService;
import com.bootdo.shop.domain.ShopDO;
import com.bootdo.shop.service.ShopService;
import com.bootdo.stock.domain.StockDO;
import com.bootdo.stock.service.StockService;
import com.bootdo.system.domain.UserDO;
@Service
public class analysisExcel {
	//门店 
		@Autowired
		private ShopService shopService;
		//商品
		@Autowired
		private OutStorageService outStorageService;
		@Autowired
		private OutStorageDetailService outStorageDetailService;
		@Autowired
		private DictService dictService;
		@Autowired
		private StockService stockService;
		@Autowired
		private GoodsInfoService goodsInfoService;
	/**
	 * 判断excel 格式返回 workbook
	 * 
	 * @param path
	 * @return
	 */
	public Workbook workbook(String fileName,InputStream inputStream) {
		Workbook workbook = null;// excel 表格
		if (fileName == null) {
			return null;
		}
		String extString = fileName.substring(fileName.indexOf("."));// 截取excil的后缀
		InputStream is = null;// 读取文件
		try {
			is = inputStream;
			if (".xls".equals(extString)) {
				workbook = new HSSFWorkbook(is);
			} else if (".xlsx".equals(extString)) {
				workbook = new XSSFWorkbook(is);
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return workbook;
	}
	//格式化单元格数据
	public Object getCellValue(Cell cell) {
		Object object = null;
		if (cell != null) {
			object = cell.toString();

		}
		return object;
	}
	public ExcelResult excel(String fileName,InputStream is,Long ProviderId) {
		ExcelResult excelResult = new ExcelResult();
		Workbook workbook = null; // excel 表格
		Sheet sheet = null;//
		Row row = null;// 每一行
		List<Map<String, String>> list = null;// 要存放的数据集合
		String cellData = null; // 每一格数据
		String outstorageDate = null;// 建单时间
		String oddNumbers = null;// 单号
		String shopName = null; // 门店名称
		String linkman = null;// 联系人
		String phone = null;// 电话
		String address = null;// 地址
		String totalQuantity = null;// 数量总计
		String totalMoney = null;// 金额总计
		OutStorageDO do1 = new OutStorageDO();//订单
		List<OutStorageDetailDO> detailDOs = new ArrayList<>(); //订单详情
		DictDO dictDO = null;// 数据字典实体
		Map<String, Object> m  = null;//查询map 
		String columns[] = { "datilGoodsid", "货品名称", // 1
				"datilStandard", "datilPricingMannerName", "datilAmount", "原始单价", // 5
				"折扣率", // 6
				"price", "totalPrices", "赠品", // 9
				"产品批号", // 10
				"productionDate", "有效期至", // 12
				"expirationDate", "退货数量", // 14
				"引用单号", // 15
				"datilRemark" };
		workbook = this.workbook(fileName,is);
		if (workbook != null) {
			list = new ArrayList<Map<String, String>>();
			sheet = workbook.getSheetAt(0);// 获取第一个 sheet
			int rowNumber = sheet.getPhysicalNumberOfRows();// 获取有数剧的最大行号
			row = sheet.getRow(1);
			outstorageDate = row.getCell(8).toString();// 建单时间
			oddNumbers = row.getCell(10).toString();// 单号
			row = sheet.getRow(2);
			shopName = row.getCell(1).toString(); // 门店名称
			row = sheet.getRow(3);
			linkman = row.getCell(1).toString();// 联系人
			phone = row.getCell(3).toString();// 电话
			row = sheet.getRow(6);
			address = row.getCell(1).toString();// 地址
			ok: for (int i = 10; i < rowNumber; i++) {
				Map<String, String> map = new LinkedHashMap<String, String>();
				row = sheet.getRow(i);// 循环获取每一行
				if (row != null) {
					int colomNumber = row.getPhysicalNumberOfCells();
					for (int j = 0; j < colomNumber; j++) {
						cellData = (String) this.getCellValue(row.getCell(j));
						if ( j == 5 || j == 6 || j == 9 || j == 10 || j == 12 || j == 14 || j == 15)
							continue;
						if (cellData.equals("合计")) {
							totalQuantity = row.getCell(4).toString();
							totalMoney = row.getCell(8).toString();
							break ok;
						}
						map.put(columns[j], cellData);
					}
				} else
					break;
				list.add(map);
			}
			Date d = null;
			try
			{
				d = new SimpleDateFormat("yyyy-MM-dd").parse(outstorageDate);
			}catch(
			ParseException e)
			{
				e.printStackTrace();
			}
			do1.setAddress(address);
			do1.setOutstorageOrderid(1001002L);// 加急状态 不急
			do1.setProviderId(ProviderId);// 供应商id；
			do1.setOutstorageDate(d);
			do1.setOddNumbers(oddNumbers);
			do1.setLinkman(linkman);
			do1.setPhone(phone);
			do1.setTotalMoney(totalMoney);
			do1.setTotalQuantity(totalQuantity);
			do1.setOutstorageStatus("12201");
			m= new HashMap<>();
			m.put("shopName", shopName);
			if(shopService.list(m).size()<=0) {
				excelResult.setShopName(shopName);
				return excelResult;
			}
			ShopDO shop = shopService.list(m).get(0);
			do1.setOutstorageShopid(shop.getShopId().toString());
			outStorageService.save(do1);
			for (Map<String, String> map : list) {
				OutStorageDetailDO datilDO = new OutStorageDetailDO();
				datilDO.setDatilEamount(-1);
				GoodsInfoDO goodinfo = null;
				for (String e : map.keySet()) {
					
					if (e.equals("datilGoodsid")) {// 设置商品
						m.clear();
						m.put("goodsCode", map.get(e));
						m.put("providerId", ProviderId);
						List<GoodsInfoDO> good = goodsInfoService.list(m);
						if(good.size()<=0) {
							excelResult.setGoodNum(map.get("货品名称"));
							outStorageService.remove(do1.getOutstorageId());
							return excelResult;
						}
						goodinfo = good.get(0);
						datilDO.setDatilGoodsid(goodinfo.getGoodsId());
					} else if (e.equals("datilStandard")) {// 设置规格
						datilDO.setDatilStandard(map.get(e));
					} else if (e.equals("datilPricingMannerName")) {
						datilDO.setDatilPricingManner(map.get(e));// 设置单位
					} else if (e.equals("datilAmount")) {
						StockDO stock = 	stockService.getStockByGsId(goodinfo.getGoodsId());
						Integer num = new Integer(map.get(e));
						if(stock.getStockOutNum()<new Integer(map.get(e))) {//判断库存数量
							excelResult.setGoodNum(map.get("货品名称"));
							excelResult.setMessage("库存数量不足");
							outStorageService.remove(do1.getOutstorageId());
							return excelResult;
						}
						datilDO.setDatilAmount(num);
						stockService.update(goodinfo.getGoodsId(), num*(-1), num*(-1), 0, 0);
					} else if (e.equals("price")) {
						datilDO.setPrice(map.get(e));
					} else if (e.equals("totalPrices")) {
						datilDO.setTotalPrices(map.get(e));
					} else if (e.equals("productionDate")) {
						datilDO.setProductionDate(map.get(e));
					} else if (e.equals("expirationDate")) {
						datilDO.setExpirationDate(map.get(e));
					} else {
						datilDO.setDatilRemark(map.get(e));
					}
				}
				detailDOs.add(datilDO);
			}
			for (OutStorageDetailDO	 detailDO : detailDOs) {
				detailDO.setOutId(do1.getOutstorageId());
			}
			outStorageDetailService.batchInserts(detailDOs);
			do1.setDos(detailDOs);
		}
		excelResult.setOutStorageDO(do1);
		return excelResult;
	}
}
